package Dao.Impl;

import Dao.PetDao;
import Entity.pet;
import Util.DBUtil;

import java.sql.*;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;

public class PetDaoImpl implements PetDao {
    @Override
    public List<pet> AllGetPet() throws SQLException {
            Connection conn = DBUtil.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM  pet");
            List<pet> pets = new ArrayList<pet>();
            pet p = null;
            while(rs.next()) {
                p=new pet();
                p.setId(rs.getInt("id"));
                p.setName(rs.getString("name"));
                p.setTypename(rs.getString("typename"));
                p.setHealth(rs.getInt("health"));
                p.setLove(rs.getInt("love"));
                p.setBirthday(rs.getDate("birthday"));
                p.setOwner_id(rs.getInt("owner_id"));
                p.setStore_id(rs.getInt("store_id"));
                p.setPrice(rs.getInt("price"));
                pets.add(p);
            }

            return pets;
    }

    @Override
    public pet AllGetPet(int id) throws SQLException {
        Connection conn = DBUtil.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM  pet where id="+id);
        pet p = null;
        rs.next();
            p=new pet();
            p.setId(rs.getInt("id"));
            p.setName(rs.getString("name"));
            p.setTypename(rs.getString("typename"));
            p.setHealth(rs.getInt("health"));
            p.setLove(rs.getInt("love"));
            p.setBirthday(rs.getDate("birthday"));
            p.setOwner_id(rs.getInt("owner_id"));
            p.setStore_id(rs.getInt("store_id"));
            p.setPrice(rs.getInt("price"));


        return p;
    }

    @Override
    public List<pet> AllPetByowner() throws SQLException {
        Connection conn = DBUtil.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM  pet where owner_id is null ");
        List<pet> pets = new ArrayList<pet>();
        pet p = null;
        while(rs.next()) {
            p=new pet();
            p.setId(rs.getInt("id"));
            p.setName(rs.getString("name"));
            p.setTypename(rs.getString("typename"));
            p.setHealth(rs.getInt("health"));
            p.setLove(rs.getInt("love"));
            p.setBirthday(rs.getDate("birthday"));
            p.setOwner_id(rs.getInt("owner_id"));
            p.setStore_id(rs.getInt("store_id"));
            p.setPrice(rs.getInt("price"));
            pets.add(p);
        }

        return pets;
    }

    public List<pet> AllPetBystore(int id) throws SQLException {
        Connection conn = DBUtil.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM  pet where store_id = "+id);
        List<pet> pets = new ArrayList<pet>();
        pet p = null;
        while(rs.next()) {
            p=new pet();
            p.setId(rs.getInt("id"));
            p.setName(rs.getString("name"));
            p.setTypename(rs.getString("typename"));
            p.setHealth(rs.getInt("health"));
            p.setLove(rs.getInt("love"));
            p.setBirthday(rs.getDate("birthday"));
            p.setOwner_id(rs.getInt("owner_id"));
            p.setStore_id(rs.getInt("store_id"));
            p.setPrice(rs.getInt("price"));
            pets.add(p);
        }

        return pets;
    }

    @Override
    public List<pet> AllPetByyouowner(int id) throws SQLException {
        Connection conn = DBUtil.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM  pet where owner_id ="+id);
        List<pet> pets = new ArrayList<pet>();
        pet p = null;
        while(rs.next()) {
            p=new pet();
            p.setId(rs.getInt("id"));
            p.setName(rs.getString("name"));
            p.setTypename(rs.getString("typename"));
            p.setHealth(rs.getInt("health"));
            p.setLove(rs.getInt("love"));
            p.setBirthday(rs.getDate("birthday"));
            p.setOwner_id(rs.getInt("owner_id"));
            p.setStore_id(rs.getInt("store_id"));
            p.setPrice(rs.getInt("price"));
            pets.add(p);
        }

        return pets;
    }

    @Override
    public void add(pet p) throws SQLException, ParseException {
        Connection conn = DBUtil.getConnection();

        String sql = "insert into account (id, name, typename, " +
                "      health, love, birthday, " +
                "      owner_id, store_id, price)" +
                "    values (null,?,?,?,?,?,?,?,?)";

        PreparedStatement ptmt = conn.prepareStatement(sql); //预编译SQL，减少sql执行

        ptmt.setString(1, p.getName());
        ptmt.setString(2, p.getTypename());
        ptmt.setInt(3, p.getHealth());
        ptmt.setInt(4, p.getLove());
        ptmt.setDate(5,(java.sql.Date) new Date(p.getBirthday().getTime()));
        ptmt.setInt(6,p.getOwner_id() );
        ptmt.setInt(7, p.getStore_id());
        ptmt.setInt(8, p.getPrice());

        //执行
        ptmt.execute();

    }

    @Override
    public void update(pet p) throws SQLException {
        Connection conn = DBUtil.getConnection();

        String sql = " update pet" +
                "    set name = ?," +
                "      typename = ?," +
                "      health = ?," +
                "      love = ?," +
                "      birthday = ?," +
                "      owner_id = ?," +
                "      store_id = ?," +
                "      price = ?" +
                "    where id = ?";

        PreparedStatement ptmt = conn.prepareStatement(sql); //预编译SQL，减少sql执行

        ptmt.setString(1, p.getName());
        ptmt.setString(2, p.getTypename());
        ptmt.setInt(3, p.getHealth());
        ptmt.setInt(4, p.getLove());
        ptmt.setDate(5,(java.sql.Date) new Date(p.getBirthday().getTime()));
        ptmt.setInt(6, p.getOwner_id());
        ptmt.setInt(7, p.getStore_id());
        ptmt.setInt(8, p.getPrice());
        ptmt.setInt(9, p.getId());

        //执行
        ptmt.execute();
    }

    @Override
    public void idnull(pet p) throws SQLException {
        Connection conn = DBUtil.getConnection();

        String sql = " update pet" +
                "    set name = ?," +
                "      typename = ?," +
                "      health = ?," +
                "      love = ?," +
                "      birthday = ?," +
                "      owner_id = null," +
                "      store_id = ?," +
                "      price = ?" +
                "    where id = ?";

        PreparedStatement ptmt = conn.prepareStatement(sql); //预编译SQL，减少sql执行

        ptmt.setString(1, p.getName());
        ptmt.setString(2, p.getTypename());
        ptmt.setInt(3, p.getHealth());
        ptmt.setInt(4, p.getLove());
        ptmt.setDate(5,(java.sql.Date) new Date(p.getBirthday().getTime()));
        ptmt.setInt(6, p.getStore_id());
        ptmt.setInt(7, p.getPrice());
        ptmt.setInt(8, p.getId());

        //执行
        ptmt.execute();
    }

    @Override
    public boolean delete(pet p) {
        return false;
    }
}
